namespace ZetaLib.Core.Data
{
	#region Using directives.
	// ----------------------------------------------------------------------

	using System;
	using System.Data;
	using System.Data.Odbc;
	using System.Runtime.InteropServices;

	// ----------------------------------------------------------------------
	#endregion

	/////////////////////////////////////////////////////////////////////////

	/// <summary>
	/// Class that simplifies the adding/updating of recordsets with ADO.NET.
	/// See the description of the base class for usage details
	/// </summary>
	[ComVisible( false )]
	public sealed class AdoNetOdbcUpdater :
		AdoNetBaseUpdater<
		OdbcCommand,
		OdbcCommandBuilder,
		OdbcConnection,
		OdbcDataAdapter,
		OdbcParameter,
		AdoNetOdbcParamCollection>
	{
		#region Constructors.
		// ------------------------------------------------------------------

		/// <summary>
		/// Default constructor.
		/// Uses the default connection string.
		/// </summary>
		public AdoNetOdbcUpdater()
		{
		}

		/// <summary>
		/// Constructor that executes a SQL query.
		/// Uses the default connection string.
		/// </summary>
		/// <param name="query">The SQL query to execute.</param>
		public AdoNetOdbcUpdater(
			string query )
		{
			Open( query );
		}

		/// <summary>
		/// Constructor that executes a SQL query.
		/// </summary>
		/// <param name="connectionString">The connection string to use.</param>
		/// <param name="query">The SQL query to execute.</param>
		public AdoNetOdbcUpdater(
			SmartConnectionString connectionString,
			string query )
		{
			Open( connectionString, query );
		}

		/// <summary>
		/// Constructor that executes a SQL stored procedure.
		/// Uses the default connection string.
		/// </summary>
		/// <param name="spName">Name of the sp.</param>
		/// <param name="spParameters">The sp parameters.</param>
		public AdoNetOdbcUpdater(
			string spName,
			AdoNetOdbcParamCollection spParameters )
		{
			Open( spName, spParameters );
		}

		/// <summary>
		/// Constructor that executes a SQL stored procedure.
		/// </summary>
		/// <param name="connectionString">The connection string to use.</param>
		/// <param name="spName">Name of the sp.</param>
		/// <param name="spParameters">The sp parameters.</param>
		public AdoNetOdbcUpdater(
			SmartConnectionString connectionString,
			string spName,
			AdoNetOdbcParamCollection spParameters )
		{
			Open( connectionString, spName, spParameters );
		}

		// ------------------------------------------------------------------
		#endregion

		#region Open routines.
		// ------------------------------------------------------------------

		/// <summary>
		/// Open the given SQL query.
		/// </summary>
		/// <param name="connectionString">The connection string to use.</param>
		/// <param name="query">The SQL query to execute.</param>
		public override void Open(
			SmartConnectionString connectionString,
			string query )
		{
			// Remember query, for logging error in Update().
			this.connectionString = connectionString;
			this.query = query;

			AdoNetOdbcHelper.TraceSql( query, true, dataSet );
			try
			{
				/*tableName = DetectTableName( query );*/
				query = AdoNetOleDBHelper.CSManager.CheckGet(
					connectionString ).ReplaceQueryBoolean( query );

				connection = new OdbcConnection( connectionString.ConnectionString );
				dataAdapter = new OdbcDataAdapter( query, connection );

				dataAdapter.RowUpdated +=
					new OdbcRowUpdatedEventHandler(
					dataAdapter_RowUpdated );

				commandBuilder = new OdbcCommandBuilder( DataAdapter );
				commandBuilder.QuotePrefix = @"[";
				commandBuilder.QuoteSuffix = @"]";

				dataSet = new DataSet();
				dataAdapter.Fill( dataSet );
			}
			catch ( Exception x )
			{
				AdoNetOdbcHelper.TraceSqlError( query, x );
				throw;
			}
			AdoNetOdbcHelper.TraceSql( query, false, dataSet );
		}

		/// <summary>
		/// Opens the given SQL stored procedure.
		/// </summary>
		/// <param name="connectionString">The connection string to use.</param>
		/// <param name="spName"></param>
		/// <param name="spParameters"></param>
		public override void Open(
			SmartConnectionString connectionString,
			string spName,
			AdoNetOdbcParamCollection spParameters )
		{
			this.connectionString = connectionString;
			connection = new OdbcConnection( connectionString.ConnectionString );

			spName = AdoNetOleDBHelper.CSManager.CheckGet(
				connectionString ).ReplaceQueryBoolean( spName );

			command = new OdbcCommand( spName, connection );
			command.CommandType = AdoNetOdbcHelper.DetectCommandType( spName );

			// Apply command timeouts, if any.
			AdoNetOdbcHelper.Current.CheckSetCommandTimeout( command );

			if ( spParameters != null )
			{
				foreach ( OdbcParameter parameter in spParameters )
				{
					command.Parameters.Add( parameter );
				}
			}

			connection.Open();
			dataAdapter = new OdbcDataAdapter( command );

			dataAdapter.RowUpdated +=
				new OdbcRowUpdatedEventHandler(
				dataAdapter_RowUpdated );

			commandBuilder = new OdbcCommandBuilder( dataAdapter );
			commandBuilder.QuotePrefix = @"[";
			commandBuilder.QuoteSuffix = @"]";

			// Apply command timeouts, if any.
			AdoNetOdbcHelper.Current.CheckSetCommandTimeout( dataAdapter.DeleteCommand );
			AdoNetOdbcHelper.Current.CheckSetCommandTimeout( dataAdapter.InsertCommand );
			AdoNetOdbcHelper.Current.CheckSetCommandTimeout( dataAdapter.SelectCommand );
			AdoNetOdbcHelper.Current.CheckSetCommandTimeout( dataAdapter.UpdateCommand );

			dataSet = new DataSet();
			AdoNetOdbcHelper.TraceSql( command, true, dataSet );
			try
			{

				dataAdapter.Fill( dataSet );
			}
			catch ( Exception x )
			{
				AdoNetOdbcHelper.TraceSqlError( command, x );
				throw;
			}
			AdoNetOdbcHelper.TraceSql( command, false, dataSet );
		}

		// ------------------------------------------------------------------
		#endregion

		#region Identity routines.
		// ------------------------------------------------------------------

		/// <summary>
		/// Detectes the identity. Call this function after
		/// the Update() function returned successfully.
		/// </summary>
		/// <param name="connection"></param>
		/// <param name="tableName">The table name, as a hint,
		/// if auto-detection of the table name fails. This parameter usually
		/// is required if the SQL query that was used to open the database,
		/// was an EXECUTE of a stored procedure.</param>
		/// <returns>Returns the identity, if any.</returns>
		public override int GetIdentity(
			OdbcConnection connection,
			string tableName )
		{
			return AdoNetOdbcHelper.Current.GetIdentity(
				connection,
				tableName );
		}

		// ------------------------------------------------------------------
		#endregion

		#region Updating methods.
		// ------------------------------------------------------------------

		/// <summary>
		/// See http://www.codeproject.com/cs/database/relationaladonet.asp.
		/// </summary>
		/// <param name="sender">The source of the event.</param>
		/// <param name="e">The <see cref="System.Data.Odbc.OdbcRowUpdatedEventArgs"/> 
		/// instance containing the event data.</param>
		private void dataAdapter_RowUpdated(
			object sender,
			OdbcRowUpdatedEventArgs e )
		{
			AdoNetOdbcHelper.DatabaseType type =
				AdoNetOdbcHelper.CSManager.CheckGet(
				new SmartConnectionString( Connection.ConnectionString ) ).DatabaseType;

			// Special handling for MS Access.
			if ( type == AdoNetOdbcHelper.DatabaseType.Access )
			{
				OdbcCommand cmd = new OdbcCommand(
					@"SELECT @@IDENTITY",
					e.Command.Connection );

				object o = cmd.ExecuteScalar();

				lastIdentityValue = 0;
				if ( o != null && o != DBNull.Value )
				{
					lastIdentityValue = Convert.ToInt32( o );
				}
			}
		}

		// ------------------------------------------------------------------
		#endregion

		#region Abstracts to override.
		// ------------------------------------------------------------------

		/// <summary>
		/// Must override, return NULL for none.
		/// </summary>
		/// <value></value>
		public override string QuotePrefix
		{
			get
			{
				return @"[";
			}
		}

		/// <summary>
		/// Must override, return NULL for none.
		/// </summary>
		/// <value></value>
		public override string QuoteSuffix
		{
			get
			{
				return @"]";
			}
		}

		// ------------------------------------------------------------------
		#endregion
	}

	/////////////////////////////////////////////////////////////////////////
}